package com.example.banksys;

import DataBase.myDB;
import com.pixelduke.control.skin.FXSkins;
import javafx.beans.property.SimpleBooleanProperty;
import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.fxml.FXMLLoader;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.control.cell.TextFieldTableCell;
import javafx.stage.Stage;
import javafx.util.Callback;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class EmployeeManageController {


    ObservableList<EmployeeEntity> employeeList;
    public TextField qID;
    public ComboBox<String> Organization;
    public ComboBox<String> Type;
    public ComboBox<String> Department;
    public Button Query;
    public TableView<EmployeeEntity> resTable;
    public TableColumn<EmployeeEntity,Number> IDCol;
    public TableColumn<EmployeeEntity,String> DepCol;
    public TableColumn<EmployeeEntity,String> TypeCol;
    public TableColumn<EmployeeEntity,String> OrgCol;
    public TableColumn<EmployeeEntity,Boolean> EditCol;
    public TableColumn<EmployeeEntity,String> NameCol;

    public static SimpleBooleanProperty needRefresh=new SimpleBooleanProperty(false);


    public void initialize() throws SQLException {
        myDB database=new myDB("banksystem");
        //下面需要从已知的职员的信息中提取出已有的部门组织营业部等信息
        String sql="select distinct organization from employee";
        ResultSet OrganizationRes=database.getStatement().executeQuery(sql);
        //为组织附上选项
        Organization.getItems().add("all");
        while(OrganizationRes.next()){
            Organization.getItems().add(OrganizationRes.getString("organization"));
        }

        sql="select distinct department from employee";
        ResultSet DepartmentRes=database.getStatement().executeQuery(sql);
        //为部门附上选项
        Department.getItems().add("all");
        while(DepartmentRes.next()){
            Department.getItems().add(DepartmentRes.getString("department"));
        }
        Type.getItems().addAll("all","管理人员","业务人员");
        needRefresh.addListener(new ChangeListener<Boolean>() {
            @Override
            public void changed(ObservableValue<? extends Boolean> observableValue, Boolean oldb, Boolean newb) {
                if(!oldb&&newb){
                    employeeList= FXCollections.observableArrayList();
                    myDB mydb=new myDB("banksystem");
                    StringBuilder sql=new StringBuilder("select * from employee where 1=1");
                    boolean[] isi=new boolean[5];
                    int coun=0;
                    for (boolean item :isi) {
                        item=false;
                    }

                    //要对是否填写内容进行判断有没有，进行字符串拼接
                    if(!(qID.getText().isEmpty()||qID.getText().equals("all"))){
                        isi[1]=true;
                        coun++;
                        sql.append(" and employee_ID = ?");
                    }

                    if(!(Organization.getValue() == null||Organization.getValue().equals("all"))){
                        isi[2]=true;
                        coun++;
                        sql.append(" and organization = ?");
                    }

                    if(!(Department.getValue() == null||Department.getValue().equals("all"))){
                        isi[3]=true;
                        coun++;
                        sql.append(" and department = ?");
                    }

                    if(!(Type.getValue() == null||Type.getValue().equals("all"))){
                        isi[4]=true;
                        coun++;
                        sql.append(" and type = ?");
                    }
                    PreparedStatement preparedStatement= null;
                    try {
                        preparedStatement = mydb.getConnection().prepareStatement(String.valueOf(sql));


                    for(int i=1;i<=coun;i++){
                        if(isi[1]){
                            preparedStatement.setLong(i,Long.parseLong(qID.getText()));
                            isi[1]=false;
                            continue;
                        }
                        if(isi[2]){
                            preparedStatement.setString(i,Organization.getValue().toString());
                            isi[2]=false;
                            continue;
                        }if(isi[3]){
                            preparedStatement.setString(i, Department.getValue().toString());
                            isi[3]=false;
                            continue;
                        }if(isi[4]){
                            preparedStatement.setString(i,Type.getValue().toString());
                            isi[4]=false;
                        }
                    }
                    ResultSet Multires=preparedStatement.executeQuery();
                    while(Multires.next()){
                        EmployeeEntity currEmployee=new EmployeeEntity();
                        currEmployee.setEmployeeID(Multires.getLong(1));
                        currEmployee.setOrganization(Multires.getString(2));
                        currEmployee.setDepartment(Multires.getString(3));
                        currEmployee.setType(Multires.getString(4));
                        currEmployee.setName(Multires.getString(6));
                        employeeList.add(currEmployee);
                    }
                    resTable.setItems(employeeList);
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                    }
                    needRefresh.set(false);
                }
            }
        });
        this.MultiQuery();
    }

    public void MultiQuery() throws SQLException {
        this.MultiPartlyQuery();

        //注意，这个callback返回的貌似就是显示在cell中的元素，而需要覆写的call中传入的参数则是当前行的一个celldata，根据这个celldata确定好要显示什么
        IDCol.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<EmployeeEntity, Number>, ObservableValue<Number>>() {
            @Override
            public ObservableValue<Number> call(TableColumn.CellDataFeatures<EmployeeEntity, Number> employeeEntityNumberCellDataFeatures) {
                return employeeEntityNumberCellDataFeatures.getValue().getEmployeeID();
            }
        });
        DepCol.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<EmployeeEntity, String>, ObservableValue<String>>() {
            @Override
            public ObservableValue<String> call(TableColumn.CellDataFeatures<EmployeeEntity, String> employeeEntityStringCellDataFeatures) {
                return employeeEntityStringCellDataFeatures.getValue().getDepartment();
            }
        });

        OrgCol.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<EmployeeEntity, String>, ObservableValue<String>>() {
            @Override
            public ObservableValue<String> call(TableColumn.CellDataFeatures<EmployeeEntity, String> employeeEntityStringCellDataFeatures) {
                return employeeEntityStringCellDataFeatures.getValue().getOrganization();
            }
        });

        TypeCol.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<EmployeeEntity, String>, ObservableValue<String>>() {
            @Override
            public ObservableValue<String> call(TableColumn.CellDataFeatures<EmployeeEntity, String> employeeEntityStringCellDataFeatures) {
                return employeeEntityStringCellDataFeatures.getValue().getType();
            }
        });

        NameCol.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<EmployeeEntity, String>, ObservableValue<String>>() {
            @Override
            public ObservableValue<String> call(TableColumn.CellDataFeatures<EmployeeEntity, String> employeeEntityStringCellDataFeatures) {
                return employeeEntityStringCellDataFeatures.getValue().getName();
            }
        });
        //接下来是给最后一个edit进行赋值的，但是目标是让这一列中出现button，和上方不一样
        EditCol.setCellValueFactory(new Callback<TableColumn.CellDataFeatures<EmployeeEntity, Boolean>, ObservableValue<Boolean>>() {
            @Override public ObservableValue<Boolean> call(TableColumn.CellDataFeatures<EmployeeEntity, Boolean> features) {
                return new SimpleBooleanProperty(features.getValue() != null);
            }
        });

        EditCol.setCellFactory(new Callback<TableColumn<EmployeeEntity, Boolean>, TableCell<EmployeeEntity, Boolean>>() {

            @Override
            public TableCell<EmployeeEntity, Boolean> call(TableColumn<EmployeeEntity, Boolean> employeeEntityBooleanTableColumn) {

                return new TableCell<>(){
                    @Override
                    protected void updateItem(Boolean item, boolean notempty) {
                        super.updateItem(item, notempty);
                        Button button = new Button("编辑");
                        button.setOnAction(new EventHandler<ActionEvent>() {
                            @Override
                            public void handle(ActionEvent actionEvent) {
                                FXMLLoader fxmlLoader = new FXMLLoader(MainApplication.class.getResource("Edit.fxml"));
                                Scene scene = null;
                                try {
                                    scene = new Scene(fxmlLoader.load());
                                } catch (IOException e) {
                                    throw new RuntimeException(e);
                                }
                                scene.getStylesheets().add(FXSkins.getStylesheetURL());
                                EditController ctrl=fxmlLoader.getController();
                                EmployeeEntity emp=employeeList.get(getIndex());//太关键了，至关重要的一个获得index
                                ctrl.ID.setText(emp.getEmployeeID().getValue().toString());
                                ctrl.namestr=emp.getName().get();
                                ctrl.orgstr=emp.getOrganization().get();
                                ctrl.depstr=emp.getDepartment().get();
                                ctrl.typstr=emp.getType().get();
                                ctrl.name.setText(ctrl.namestr);
                                ctrl.Department.setText(ctrl.depstr);
                                ctrl.Organization.setText(ctrl.orgstr);
                                ctrl.Type.setText(ctrl.typstr);

                                Stage t=new Stage();
                                t.setScene(scene);
                                t.show();
                            }
                        });
                        if (notempty) {
                            //如果此列为空默认不添加元素
                            setText(null);
                            setGraphic(null);
                        } else {
                            this.setGraphic(button);
                        }
                    }
                };
            }
        });
    }

    public void MultiPartlyQuery() throws SQLException {
        employeeList= FXCollections.observableArrayList();
        myDB mydb=new myDB("banksystem");
        StringBuilder sql=new StringBuilder("select * from employee where 1=1");
        boolean[] isi=new boolean[5];
        int coun=0;
        for (boolean item :isi) {
            item=false;
        }

        //要对是否填写内容进行判断有没有，进行字符串拼接
        if(!(qID.getText().isEmpty()||qID.getText().equals("all"))){
            isi[1]=true;
            coun++;
            sql.append(" and employee_ID = ?");
        }

        if(!(Organization.getValue() == null||Organization.getValue().equals("all"))){
            isi[2]=true;
            coun++;
            sql.append(" and organization = ?");
        }

        if(!(Department.getValue() == null||Department.getValue().equals("all"))){
            isi[3]=true;
            coun++;
            sql.append(" and department = ?");
        }

        if(!(Type.getValue() == null||Type.getValue().equals("all"))){
            isi[4]=true;
            coun++;
            sql.append(" and type = ?");
        }
        PreparedStatement preparedStatement= mydb.getConnection().prepareStatement(String.valueOf(sql));
        for(int i=1;i<=coun;i++){
            if(isi[1]){
                preparedStatement.setLong(i,Long.parseLong(qID.getText()));
                isi[1]=false;
                continue;
            }
            if(isi[2]){
                preparedStatement.setString(i,Organization.getValue().toString());
                isi[2]=false;
                continue;
            }if(isi[3]){
                preparedStatement.setString(i, Department.getValue().toString());
                isi[3]=false;
                continue;
            }if(isi[4]){
                preparedStatement.setString(i,Type.getValue().toString());
                isi[4]=false;
            }
        }
        ResultSet Multires=preparedStatement.executeQuery();
        while(Multires.next()){
            EmployeeEntity currEmployee=new EmployeeEntity();
            currEmployee.setEmployeeID(Multires.getLong(1));
            currEmployee.setOrganization(Multires.getString(2));
            currEmployee.setDepartment(Multires.getString(3));
            currEmployee.setType(Multires.getString(4));
            currEmployee.setName(Multires.getString(6));
            employeeList.add(currEmployee);
        }
        resTable.setItems(employeeList);
    }

}
